表單結構

2023 iThome 鐵人賽

DAY 15

Modern Web

Google Apps Script 整合運用系列第 15 篇

15th鐵人賽

ugm

2023-09-18 23:22:33

179 瀏覽

分享至

表單結構

取得結構

/*========================================
  取得結構
=========================================*/
function get_stru_custom() {
  let stru = [
    { title: '流水號', type: '', form_name: 'sn', form_value: '', form_width: '', form_kind: '隱藏', form_option: '', form_validate: '', form_readme: '', form_placeholder: '' },
    { title: '客戶名稱', type: '文字', form_name: 'title', form_value: '', form_width: '4', form_kind: '簡答', form_option: '', form_validate: '必填', form_readme: '', form_placeholder: '' },
    { title: '客戶電話', type: '文字', form_name: 'tel', form_value: '', form_width: '4', form_kind: '簡答', form_option: '', form_validate: '必填', form_readme: '', form_placeholder: '' },
    { title: '客戶地址', type: '文字', form_name: 'addr', form_value: '', form_width: '4', form_kind: '簡答', form_option: '', form_validate: '必填', form_readme: '', form_placeholder: '' },
    { title: '備註', type: '文字', form_name: 'ps', form_value: '', form_width: '12', form_kind: '段落', form_option: '', form_validate: '', form_readme: '', form_placeholder: '' },
  ];
  return stru;
}

取得結構預設值


/*========================================
  取得結構預設值
=========================================*/
function get_default_value_custom() {
  let stru = get_stru_custom();
  let row = {};
  for(let i in stru){
    row[stru[i]['form_name']] = stru[i]['form_value'];
  }
  return row;
}

用流水號取得記錄 function.gs


/*========================================
  用流水號取得記錄
=========================================*/
function get_row_by_sn(sn, sheet) {
  let ss = SpreadsheetApp.getActiveSpreadsheet()
  let ws = ss.getSheetByName(sheet);
  if (ws.getLastRow() < 2) return {};//尚無資料，返回{}
  let colArray = ws.getRange(2, 1, ws.getLastRow() - 1).getValues().map(function(row){
    return row[0];
  });
  let colIndex = colArray.indexOf(Number(sn));

  if(colIndex === -1)return {}
  
  let data = ws.getSheetValues(colIndex + 2, 1, 1, ws.getLastColumn())[0];
  let stru = get_stru_custom();
  let row = {};
  for(let i in stru){
    row[stru[i]['form_name']] = data[i];
  }
  return row;
}

客戶資料表單


/*========================================
  客戶資料 表單
=========================================*/
function form_custom(e) {
  let content = '';
  let sheet = 'day2'
  // 判斷是否接收到 sn
  let sn = e.parameter.sn ? e.parameter.sn : '';

  let title = sn ? '客戶 編輯表單' : '客戶 新增表單';
  content = `<h2 class='mt-3'>${title}</h2>`;
  // 表單子樣版
  let stru = get_stru_custom();
  let row = {};
  if(sn){
    row = get_row_by_sn(sn, sheet);//用流水號取得記錄
  }else{
    row = get_default_value_custom();//取得結構預設值
  }
  content += render('form_custom', {stru: stru, row: row});

  return render('tmp', { content: content }, title);
}

樣版

form_custom.html

<!-- 
   let stru = [
    { title: '流水號', type: '', form_name: 'sn', form_value: '', form_width: '', form_kind: '隱藏', form_option: '', form_validate: '', form_readme: '', form_placeholder: '' },
    { title: '客戶名稱', type: '文字', form_name: 'title', form_value: '', form_width: '4', form_kind: '簡答', form_option: '', form_validate: '必填', form_readme: '', form_placeholder: '' },
    { title: '客戶電話', type: '文字', form_name: 'tel', form_value: '', form_width: '4', form_kind: '簡答', form_option: '', form_validate: '必填', form_readme: '', form_placeholder: '' },
    { title: '客戶地址', type: '文字', form_name: 'addr', form_value: '', form_width: '4', form_kind: '簡答', form_option: '', form_validate: '必填', form_readme: '', form_placeholder: '' },
    { title: '備註', type: '文字', form_name: 'ps', form_value: '', form_width: '12', form_kind: '段落', form_option: '', form_validate: '', form_readme: '', form_placeholder: '' },
  ];
 -->

<form action="<?!= global['url'] ?>" id="myForm" class="mb-5" method="POST">
  <div class="row">
    <? for(let i in stru){ ?>
      <? if(stru[i]['form_kind'] === '隱藏'){ ?>
        <input type="hidden" name="<?= stru[i]['form_name'] ?>" id="<?= stru[i]['form_name'] ?>" value="<?= row[stru[i]['form_name']] ?>">  
      <? }else if(stru[i]['form_kind'] === '下拉選單'){ ?>
      <? }else if(stru[i]['form_kind'] === '單選'){ ?>
      <? }else if(stru[i]['form_kind'] === '複選'){ ?>
      
      <? }else if(stru[i]['form_kind'] === '段落'){ ?> 
        <!-- 段落 -->
        <div class="col-md-<?= stru[i]['form_width'] ?> mb-3">
          <label>
            <?= stru[i]['title'] ?>
            <? if(stru[i]['form_validate'].split('|').indexOf('必填') !== -1){ ?>
              <span class="text-danger"> *</span>
            <? } ?>
            <? if(stru[i]['form_readme']){ ?>
            <span class="text-danger"> (表單說明)</span>
            <? } ?>   
          </label>
          <textarea class="form-control" name="<?= row[stru[i]['form_name']] ?>" id="<?= row[stru[i]['form_name']] ?>" rows="3" placeholder="<?= stru[i]['form_placeholder'] ?>"><?= row[stru[i]['form_name']] ?></textarea>
        </div>

      <? }else { ?>      
        <!-- 簡答  -->
        <div class="col-md-<?= stru[i]['form_width'] ?> mb-3">
          <label>
            <?= stru[i]['title'] ?>
            <? if(stru[i]['form_validate'].split('|').indexOf('必填') !== -1){ ?>
              <span class="text-danger"> *</span>
            <? } ?>
            <? if(stru[i]['form_readme']){ ?>
            <span class="text-danger"> (表單說明)</span>
            <? } ?>            
          </label>
          <input type="text" class="form-control" name="<?= stru[i]['form_name'] ?>" id="<?= stru[i]['form_name'] ?>" value="<?= row[stru[i]['form_name']] ?>" placeholder="<?= stru[i]['form_placeholder'] ?>">
        </div>

      <? } ?>
    <? } ?>
    
    <div class="col-md-12 d-grid">
      <button id="submit" type="submit" class="btn btn-primary btn-block">送出</button>
    </div>
  </div>
</form>

<script src="https://cdn.jsdelivr.net/npm/jquery-validation@1.19.5/dist/jquery.validate.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jquery-validation@1.19.5/dist/localization/messages_zh_TW.min.js"></script>

<style>
  .error{
    color:red;
  }
</style>
<script>
  $(function() {
    $("#myForm").validate({
      submitHandler: function(form) {              //驗證成功執行
        $('#submit').attr('disabled', true);       //關閉送出
        setIdAttribute('main-content', 'd-none');  //關閉主畫面
        setIdAttribute('show', 'my-5');            //開啟loading畫面
        insertData();                              //寫入函式
        document.getElementById('myForm').reset(); //表單重置
        return false;
      },
      rules: {                                     //規則
        "title": {                                 //表單 name
          "required": true,                        //必填
          // "email": true,                           //email
          // "url": true,                             //網址
          // "number": true                           //數字
        }
      },
      messages: {                                  //訊息
        "title": {                                 //表單 name
          "required": "必填",                      //必填
          // "email": "Email格式不正確",              //email
          // "url": "網址格式不正確",                 //網址
          // "number": "數字格式不正確"               //數字
        }
      }
    });
  });

  /*===================================
    寫入函式
  ===================================*/
  function insertData() {
    alert('寫入函式');
  }
  
  /*===============================
  指定 id -> class
  setIdAttribute('main-content','');      //開啟主畫面
  setIdAttribute('show','d-none');        //關閉等待畫面

  setIdAttribute('main-content','d-none');//關閉主畫面
  setIdAttribute('show','my-5');          //開啟等待畫面
  ===============================*/ 
  function setIdAttribute(id, elementClass){
    document.getElementById(id).setAttribute("class", elementClass);
  }
</script>

下拉選單、單選、複選

<!-- 
   let stru = [
    { title: '流水號', type: '', form_name: 'sn', form_value: '', form_width: '', form_kind: '隱藏', form_option: '', form_validate: '', form_readme: '', form_placeholder: '' },
    { title: '客戶名稱', type: '文字', form_name: 'title', form_value: '', form_width: '4', form_kind: '簡答', form_option: '', form_validate: '必填', form_readme: '', form_placeholder: '' },
    { title: '客戶電話', type: '文字', form_name: 'tel', form_value: '', form_width: '4', form_kind: '簡答', form_option: '', form_validate: '必填', form_readme: '', form_placeholder: '' },
    { title: '客戶地址', type: '文字', form_name: 'addr', form_value: '', form_width: '4', form_kind: '簡答', form_option: '', form_validate: '必填', form_readme: '', form_placeholder: '' },
    { title: '備註', type: '文字', form_name: 'ps', form_value: '', form_width: '12', form_kind: '段落', form_option: '', form_validate: '', form_readme: '', form_placeholder: '' },
  ];
 -->
<? let option ?>
<form action="<?!= global['url'] ?>" id="myForm" class="mb-5" method="POST">
  <div class="row">
    <? for(let i in stru){ ?>
      <? if(stru[i]['form_kind'] === '隱藏'){ ?>
        <input type="hidden" name="<?= stru[i]['form_name'] ?>" id="<?= stru[i]['form_name'] ?>" value="<?= row[stru[i]['form_name']] ?>"> 
         
      <? }else if(stru[i]['form_kind'] === '下拉選單'){ ?>        
        <!-- 下拉選單 -->
        <div class="col-md-<?= stru[i]['form_width'] ?> mb-3">
          <label class="control-label">
            <?= stru[i]['title'] ?>
            <? if(stru[i]['form_validate'].split('|').indexOf('必填') !== -1){ ?>
              <span class="text-danger"> *</span>
            <? } ?>
            <? if(stru[i]['form_readme']){ ?>
            <span class="text-danger"> (<?= stru[i]['form_readme'] ?>)</span>
            <? } ?>            
          </label>
          <select name="<?= row[stru[i]['form_name']] ?>" id="<?= row[stru[i]['form_name']] ?>" class="form-select">
            <? option = stru[i]['form_option'].split('|') ?>
            <? for(let j in option){ ?>
              <option <? if(option[j] === row[stru[i]['form_name']]){ ?>selected<? } ?>><?= option[j] ?></option>
            <? } ?>
          </select>
        </div>

      <? }else if(stru[i]['form_kind'] === '單選'){ ?>
        <!-- 單選 -->
        <div class="col-md-<?= stru[i]['form_width'] ?> mb-3">
          <label class="control-label w-100">
            <?= stru[i]['title'] ?>
            <? if(stru[i]['form_validate'].split('|').indexOf('必填') !== -1){ ?>
              <span class="text-danger"> *</span>
            <? } ?>
            <? if(stru[i]['form_readme']){ ?>
            <span class="text-danger"> (<?= stru[i]['form_readme'] ?>)</span>
            <? } ?>            
          </label>
          <? option = stru[i]['form_option'].split('|') ?>
          <? for(let j in option){ ?>          
            <div class="form-check form-check-inline">
              <input class="form-check-input" type="radio" id="<?= stru[i]['form_name'] ?><?= j ?>" value="<?= option[j] ?>" name="<?= stru[i]['form_name'] ?>" <? if(option[j] === row[stru[i]['form_name']]){ ?>checked<? } ?>>
              <label class="form-check-label" for="<?= stru[i]['form_name'] ?><?= j ?>"><?= option[j] ?></label>
            </div>
          <? } ?>
        </div>

      <? }else if(stru[i]['form_kind'] === '複選'){ ?>
        <!-- 複選 -->
        <div class="col-md-12 mb-3">
          <label class="form-label w-100">
            <?= stru[i]['title'] ?>
            <? if(stru[i]['form_validate'].split('|').indexOf('必填') !== -1){ ?>
              <span class="text-danger"> *</span>
            <? } ?>
            <? if(stru[i]['form_readme']){ ?>
            <span class="text-danger"> (<?= stru[i]['form_readme'] ?>)</span>
            <? } ?>            
          </label>
          <? option = stru[i]['form_option'].split('|') ?>
          <? for(let j in option){ ?>
            <div class="form-check form-check-inline">
              <input class="form-check-input" type="checkbox" id="<?= stru[i]['form_name'] ?><?= j ?>" value="<?= option[j] ?>" name="<?= stru[i]['form_name'] ?>" 
              <? if(row[stru[i]['form_name']].split('|').indexOf(option[j]) !== -1){ ?>checked<? } ?>
              >
              <label class="form-check-label" for="<?= stru[i]['form_name'] ?><?= j ?>"><?= option[j] ?></label>
            </div>          
          <? } ?> 
        </div>
      
      <? }else if(stru[i]['form_kind'] === '段落'){ ?> 
        <!-- 段落 -->
        <div class="col-md-<?= stru[i]['form_width'] ?> mb-3">
          <label>
            <?= stru[i]['title'] ?>
            <? if(stru[i]['form_validate'].split('|').indexOf('必填') !== -1){ ?>
              <span class="text-danger"> *</span>
            <? } ?>
            <? if(stru[i]['form_readme']){ ?>
            <span class="text-danger"> (<?= stru[i]['form_readme'] ?>)</span>
            <? } ?>            
          </label>
          <textarea class="form-control" name="<?= row[stru[i]['form_name']] ?>" id="<?= row[stru[i]['form_name']] ?>" rows="3" placeholder="<?= stru[i]['form_placeholder'] ?>"><?= row[stru[i]['form_name']] ?></textarea>
        </div>

      <? }else { ?>      
        <!-- 簡答  -->
        <div class="col-md-<?= stru[i]['form_width'] ?> mb-3">
          <label>
            <?= stru[i]['title'] ?>
            <? if(stru[i]['form_validate'].split('|').indexOf('必填') !== -1){ ?>
              <span class="text-danger"> *</span>
            <? } ?>
            <? if(stru[i]['form_readme']){ ?>
            <span class="text-danger"> (<?= stru[i]['form_readme'] ?>)</span>
            <? } ?>            
          </label>
          <input type="text" class="form-control" name="<?= stru[i]['form_name'] ?>" id="<?= stru[i]['form_name'] ?>" value="<?= row[stru[i]['form_name']] ?>" placeholder="<?= stru[i]['form_placeholder'] ?>">
        </div>

      <? } ?>
    <? } ?>
    
    <div class="col-md-12 d-grid">
      <button id="submit" type="submit" class="btn btn-primary btn-block">送出</button>
    </div>
  </div>
</form>

<script src="https://cdn.jsdelivr.net/npm/jquery-validation@1.19.5/dist/jquery.validate.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/jquery-validation@1.19.5/dist/localization/messages_zh_TW.min.js"></script>

<style>
  .error{
    color:red;
  }
</style>
<script>
  $(function() {
    $("#myForm").validate({
      submitHandler: function(form) {              //驗證成功執行
        $('#submit').attr('disabled', true);       //關閉送出
        setIdAttribute('main-content', 'd-none');  //關閉主畫面
        setIdAttribute('show', 'my-5');            //開啟loading畫面
        insertData();                              //寫入函式
        document.getElementById('myForm').reset(); //表單重置
        return false;
      },
      rules: {                                     //規則
        "title": {                                 //表單 name
          "required": true,                        //必填
          // "email": true,                           //email
          // "url": true,                             //網址
          // "number": true                           //數字
        }
      },
      messages: {                                  //訊息
        "title": {                                 //表單 name
          "required": "必填",                      //必填
          // "email": "Email格式不正確",              //email
          // "url": "網址格式不正確",                 //網址
          // "number": "數字格式不正確"               //數字
        }
      }
    });
  });

  /*===================================
    寫入函式
  ===================================*/
  function insertData() {
    alert('寫入函式');
  }
  
  /*===============================
  指定 id -> class
  setIdAttribute('main-content','');      //開啟主畫面
  setIdAttribute('show','d-none');        //關閉等待畫面

  setIdAttribute('main-content','d-none');//關閉主畫面
  setIdAttribute('show','my-5');          //開啟等待畫面
  ===============================*/ 
  function setIdAttribute(id, elementClass){
    document.getElementById(id).setAttribute("class", elementClass);
  }
</script>